#!/usr/bin/env python
# encoding: utf-8
"""
coveyPairwiseFormatter.py

Created by Brant Faircloth on 2008-01-10.
Copyright (c) 2008 Brant C. Faircloth. All rights reserved.
"""

import MySQLdb, csv, pdb

class excelSingleSpace:
    """class for csv module to work correctly"""
    delimiter = ','
    quotechar = '"'
    escapechar = None
    doublequote = True
    skipinitialspace = False
    lineterminator = '\r'

def genData(c,y,cw):
    '''get the data'''
    #pdb.set_trace()
    # get bird_id and covey_id from ttrs_covey_members for a specific year
    query = (("SELECT bird_id, covey_id FROM ttrs_covey_members WHERE year = '%s' and transient != 1") % (y))
    c.execute(query)
    bird = c.fetchall()
    header = ['bird_id','covey','P1A7A','P1A7B','P1F3A','P1F3B','P1H12A','P1H12B','P2D7A','P2D7B','PA12GA','PA12GB','PA1CA','PA1CB','PA1FA','PA1FB','PA3EA','PA3EB','PA3FA','PA3FB','PA3GA','PA3GB','PA5FA','PA5FB','PBA4A','PBA4B','PBH5A','PBH5B','PCF5A','PCF5B']
    cw.writerow(header)
    for b in bird:
        iden,covey = b
        query = (("SELECT P1A7A,P1A7B,P1F3A,P1F3B,P1H12A,P1H12B,P2D7A,P2D7B,PA12GA,PA12GB,PA1CA,PA1CB,PA1FA,PA1FB,PA3EA,PA3EB,PA3FA,PA3FB,PA3GA,PA3GB,PA5FA,PA5FB,PBA4A,PBA4B,PBH5A,PBH5B,PCF5A,PCF5B from ttrs_genotypes where bird_id = '%s'") %  (iden))
        #pdb.set_trace()
        c.execute(query)
        d = c.fetchall()
        if d == ():
            pass
        else:
            # convert 'NULL' and ' NULL' to "*"
            cleanGen = ()
            genPass = 0
            for allele in d[0]:
                if allele == 'NULL' or allele == ' NULL':
                    #pdb.set_trace()
                    cleanGen += ('*',)
                    genPass += 1
                else:
                    cleanGen += (allele,)
            row = b + cleanGen
            if genPass <= 14:
                cw.writerow(row)


def main():
    database = MySQLdb.connect("parameters")
    c = database.cursor()
    year = [2001,2002,2003]
    #year = [2001]
    for y in year:
        filename = (('%s_covey_pairwise.txt') % (y))
        handle = open(filename,'w')
        cw = csv.writer(handle, dialect = excelSingleSpace)
        genData(c,y,cw)
        handle.close()

if __name__ == '__main__':
    main()

